﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.OleDb;

namespace DAO
{
    public class PhieuNhapDAO
    {
        //public List<PhieuNhapDTO> LayThongTinPhieuNhap()
        //{
        //    string sql = "SELECT * FROM PHIEUNHAP ";
        //    OleDbConnection conn = new OleDbConnection();
        //    conn = DataProvider.ConnectDB();
        //    OleDbCommand cmd = new OleDbCommand(sql, conn);
        //    OleDbDataReader reader = cmd.ExecuteReader();
        //    List<PhieuNhapDTO> DanhSach = new List<PhieuNhapDTO>();
        //    while (reader.Read())
        //    {
        //        PhieuNhapDTO pnDTO = new PhieuNhapDTO();
        //        pnDTO.MaPN = reader.GetString(0);
        //        pnDTO.MaPD = reader.GetString(1);
        //        pnDTO.NgayNH = reader.GetDateTime(2);
        //        pnDTO.MaNV = reader.GetString(3);
        //        pnDTO.TongTien = reader.GetInt32(4);
        //        DanhSach.Add(pnDTO);
        //    }
        //    conn.Close();
        //    return DanhSach;

        //}
        //public List<PhieuNhapDTO> LayThongTinPhieuNhap(string maPN)
        //{
        //    string sql = "SELECT * FROM SANPHAM WHERE MaPN= '" + maPN + "'";
        //    OleDbConnection conn = new OleDbConnection();
        //    conn = DataProvider.ConnectDB();
        //    OleDbCommand cmd = new OleDbCommand(sql, conn);
        //    OleDbDataReader reader = cmd.ExecuteReader();
        //    List<PhieuNhapDTO> DanhSach = new List<PhieuNhapDTO>();
        //    while (reader.Read())
        //    {
        //        PhieuNhapDTO pnDTO = new PhieuNhapDTO();
        //        pnDTO.MaPN = reader.GetString(0);
        //        pnDTO.MaPD = reader.GetString(1);
        //        pnDTO.NgayNH = reader.GetDateTime(2);
        //        pnDTO.MaNV = reader.GetString(3);
        //        pnDTO.TongTien = reader.GetInt32(4);
        //        DanhSach.Add(pnDTO);
        //    }
        //    return DanhSach;
        //}
        public string LayMaPNLonNhat() // Lay mã phieu ban lon nhat
        {
            string sql = "SELECT TOP  1, MaPN FROM PhieuNhap  ORDER BY MaPN DESC";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
          //  CTPhieuNhapDTO ctpnDTO = new CTPhieuNhapDTO();// sai ne
            PhieuNhapDTO pnDTO = new PhieuNhapDTO();
            while (reader.Read())
            {
                pnDTO.MaPN= reader.GetString(0);
            }
            conn.Close();
            return pnDTO.MaPN;
        }
        public string NextID(string lastID, string prefixID)
        {
            if (lastID == null)
            {
                return prefixID + "001";  // fixwidth default
            }
            int nextID = int.Parse(lastID.Remove(0, prefixID.Length)) + 1;
            int lengthNumerID = lastID.Length - prefixID.Length;
            string zeroNumber = "";
            for (int i = 1; i <= lengthNumerID; i++)
            {
                if (nextID < Math.Pow(10, i))
                {
                    for (int j = 1; j <= lengthNumerID - i; i++)
                    {
                        zeroNumber += "0";
                    }
                    return prefixID + zeroNumber + nextID.ToString();
                }
            }
            return prefixID + nextID;

        }
        public string TaoMaPNMoi()
        {
            string LastID = LayMaPNLonNhat();
            return NextID(LastID, "PN");
        }

        public int ThemPhieuNhap(PhieuNhapDTO pnDTO)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "INSERT INTO PHIEUNHAP(MaPN,MaPD,NgayNH,MaNV,TongTien) ";
            sql = sql + " VALUES(@MaPN,@MaPD,@NgayNH,@MaNV,@TongTien) ";  // thu tu tren nay 
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para = cmd.Parameters.Add("@MaPN", OleDbType.VarChar);
            para.Value = pnDTO.MaPN; // sao null   /1
            para = cmd.Parameters.Add("@MaPD", OleDbType.VarChar);
            para.Value = pnDTO.MaPD; //2
            para = cmd.Parameters.Add("@NgayNH", OleDbType.DBDate);

            para.Value = pnDTO.NgayNH; //3           
            para = cmd.Parameters.Add("@MaNV", OleDbType.VarChar);
            para.Value = pnDTO.MaNV; //4
            para = cmd.Parameters.Add("@TongTien", OleDbType.Double); // dang la kieu gi?
            para.Value = pnDTO.TongTien; //5
            int res = cmd.ExecuteNonQuery();
            conn.Close();
            if (res >= 0)
                return 1; // thành công
            return 0; // không thành công

        }
        public double BaoCaoTienChi(string Nam)
        {

            string sql = "SELECT SUM(TongTien) FROM PhieuNhap WHERE Year(NgayNH) = '"+Nam+"'  ";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            PhieuNhapDTO pnDTO = new PhieuNhapDTO();
            //double tongtien;
            while (reader.Read())
            {
                pnDTO.TongTien = reader.GetDouble(0);
            }

            conn.Close();
            return pnDTO.TongTien;

        }
        public double BaoCaoTienChiThang(string Thang,string Nam)
        {
            string sql = "SELECT SUM(TongTien) FROM PhieuNhap WHERE Month(NgayNH) = '" +Thang+ "' and Year(NgayNH) = '" +Nam+ "' ";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            PhieuNhapDTO pnDTO = new PhieuNhapDTO();
            while (reader.Read())
            {
                if (reader.IsDBNull(0))
                    pnDTO.TongTien = 0;
                else
                    //pnDTO.TongTien = Convert.ToDouble(reader.GetString(0)); // tren acess tra ve chuoi hay la int
                    pnDTO.TongTien = reader.GetDouble(0);
            }
            conn.Close();
            return pnDTO.TongTien;
        }

        public List<PhieuNhapDTO> TheoDoiNhapHang(string Thang, string Nam)
        { 
            string sql = "SELECT * FROM PHIEUNHAP WHERE Month(NgayNH) = '"+Thang+"' and Year(NgayNH) = '"+Nam+"'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<PhieuNhapDTO> DanhSach = new List<PhieuNhapDTO>();
            while (reader.Read())
            {
                if (reader.IsDBNull(0) || reader.IsDBNull(1) || reader.IsDBNull(2) ||
                    reader.IsDBNull(3) || reader.IsDBNull(4))
                    DanhSach = null;
                else
                {
                    PhieuNhapDTO pnDTO = new PhieuNhapDTO();
                    pnDTO.MaPN = reader.GetString(0);
                    pnDTO.MaPD = reader.GetString(1);
                    pnDTO.NgayNH = reader.GetDateTime(2);
                    pnDTO.MaNV = reader.GetString(3);
                    pnDTO.TongTien = reader.GetDouble(4);
                    DanhSach.Add(pnDTO);
                }

            }
            conn.Close();
            return DanhSach;
        }

    }
}
